﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LotteryVote.Entity;
using System.Data.SqlClient;

namespace LotteryVote.Data
{
    public class SettleResultDataAccess : DataBase
    {
        public void Insert(SettleResult result)
        {
            string sql = string.Format(@"INSERT INTO {0} ({1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14}) VALUES
(@{1},@{2},@{3},@{4},@{5},@{6},@{7},@{8},@{9},@{10},@{11},@{12},@{13},@{14}) SELECT IDENT_CURRENT('{0}')", SettleResult.TABLENAME,
            SettleResult.USERID, SettleResult.COMPANYID, SettleResult.ORDERCOUNT, SettleResult.BETTURNOVER, SettleResult.TOTALCOMMISSION, SettleResult.SHARERATE,
            SettleResult.COMMISSION, SettleResult.WINLOST, SettleResult.TOTALWINLOST, SettleResult.UPPERSHARERATE, SettleResult.UPPERWINLOST,
            SettleResult.UPPERCOMMISSION, SettleResult.UPPERTOTALWINLOST, SettleResult.COMPANYWINLOST);
            object id = base.ExecuteScalar(sql, new SqlParameter(SettleResult.USERID, result.UserId),
                new SqlParameter(SettleResult.COMPANYID, result.CompanyId),
                new SqlParameter(SettleResult.ORDERCOUNT, result.OrderCount),
                new SqlParameter(SettleResult.BETTURNOVER, result.BetTurnover),
                new SqlParameter(SettleResult.TOTALCOMMISSION, result.TotalCommission),
                new SqlParameter(SettleResult.SHARERATE, result.ShareRate),
                new SqlParameter(SettleResult.COMMISSION, result.Commission),
                new SqlParameter(SettleResult.WINLOST, result.WinLost),
                new SqlParameter(SettleResult.TOTALWINLOST, result.TotalWinLost),
                new SqlParameter(SettleResult.UPPERSHARERATE, result.UpperShareRate),
                new SqlParameter(SettleResult.UPPERWINLOST, result.UpperWinLost),
                new SqlParameter(SettleResult.UPPERCOMMISSION, result.UpperCommission),
                new SqlParameter(SettleResult.UPPERTOTALWINLOST, result.UpperTotalWinLost),
                new SqlParameter(SettleResult.COMPANYWINLOST, result.CompanyWinLost));
        }
        public void Delete(int companyId, DateTime date)
        {
            string sql = string.Format(@"DELETE {0} WHERE {1}=@{1} AND DAY({2})=DAY(@{2})", SettleResult.TABLENAME, SettleResult.COMPANYID, SettleResult.CREATETIME);
            base.ExecuteNonQuery(sql, new SqlParameter(SettleResult.COMPANYID, companyId),
                new SqlParameter(SettleResult.CREATETIME, date));
        }

        public IList<SettleResult> ListSettleResult(int userId, DateTime date)
        {
            string sql = string.Format(@"SELECT UR.*,SR.* FROM {0} SR
JOIN {1} UR ON UR.{2}=SR.{3}
WHERE UR.{4}=@{4} AND DAY(SR.{5})=DAY(@{5})", SettleResult.TABLENAME, User.TABLENAME,
                User.USERID, SettleResult.USERID, User.PARENTID, SettleResult.CREATETIME);
            return base.ExecuteList<SettleResult>(sql, new SqlParameter(User.PARENTID, userId),
                new SqlParameter(SettleResult.CREATETIME, date));
        }
        public IList<SettleResult> ListSettleResult(int userId, DateTime fromDate, DateTime toDate)
        {
            string sql = string.Format(@"SELECT UR.*,SR.* FROM {0} SR
JOIN {1} UR ON UR.{2}=SR.{3}
WHERE UR.{4}=@{4} AND (DAY(SR.{5}) BETWEEN DAY(@FromDate) AND DAY(@ToDate))", SettleResult.TABLENAME, User.TABLENAME,
               User.USERID, SettleResult.USERID, User.PARENTID, SettleResult.CREATETIME);
            return base.ExecuteList<SettleResult>(sql, new SqlParameter(User.PARENTID, userId),
                new SqlParameter("FromDate", fromDate),
                new SqlParameter("ToDate", toDate));
        }

        /// <summary>
        /// 获取指定用户订单中特定角色的总佣金.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="role">The role.</param>
        /// <returns></returns>
        public decimal GetOrderCommissionAmount(User member, SystemRole role, int companyId, DateTime date)
        {
            string sql = string.Format(@"select sum(ac.CommAmount) from {0} bo
join {1} bs on bs.{3}=bo.{4}
join {2} ac on ac.{5}=bo.{6}
where bs.{7}=@{7} and ac.{8}=@{8} and DAY(bs.{9})=DAY(@{9}) AND {10}=@{10}", BetOrder.TABLENAME, BetSheet.TABLENAME, OrderAncestorCommInfo.TABLENAME,
            BetSheet.SHEETID, BetOrder.SHEETID, OrderAncestorCommInfo.ORDERID, BetOrder.ORDERID, BetSheet.USERID, OrderAncestorCommInfo.ROLEID,
            BetSheet.CREATETIME, BetOrder.COMPANYID);
            object commission = base.ExecuteScalar(sql, new SqlParameter(BetSheet.USERID, member.UserId),
                new SqlParameter(OrderAncestorCommInfo.ROLEID, (int)role),
                new SqlParameter(BetSheet.CREATETIME, date),
                new SqlParameter(BetOrder.COMPANYID, companyId));
            return Convert.ToDecimal(commission);
        }
    }
}
